Access Notes

Goal

To explore the Excel and Access database with the following items in mind.

1. A detailed analysis report for each of the seven priority databases,
2. A proposed ERD for each of those databases,
3. User interface strategy document that outlines navigation, key pages, and visual style,
4. Draft user interfaces for review, iteration, and agreement,
5. Draft system design for review, iteration, and agreement,
6. A detailed project plan for Phase 1 along the list of deliverables.

Process

The access database are an issue they are outdated and need to be in a more modern format. We do not want to louse any of the data that has been used for years but we will not stick with access. Therefore pulling the information out of the access database is important.

1. VBA code to pull data out of an access database and place it into a text file.
2. Text files that and be parsed using python into sqlite metrics.

Issues

During the exploration of the databases the following issues have been noticed.

Queries can refrence other queries in the database.

Running in admin when using Python code for ORSANCO_Fish database

When running python code for the ORSANCO_Fish database you need to make sure that you are in admin mode in the terminal otherwise you will get admin error. This is mostly irrelevant as we are not using VBA code to generate a text file and from there using python code to place the tables into the sqlite database.

If we do not have permission for the database

When running VBA code in access there can be a compile error User-defined type not defined. This happens in the WSBacteria and CleanMetals database. This happens because we do not have permission. The easiest way to work around this is to migrate the tables and queries to a new access database. It is a short process and then run the vba code against the new database.

To export tables and queries from an old Access database to a new Access database, you can use the Microsoft Access application itself. Here are the steps to do this:

1. Open Microsoft Access:
   1. Launch Microsoft Access on your computer.
2. Create a New Database (if needed):
   1. If you want to create a new database for the exported tables and queries, go to "File" > "New" and choose "Blank Database."
   2. Enter a name for the new database and specify a location if prompted.
3. Import Tables and Queries:
   1. In the new database, go to the "External Data" tab on the ribbon.
4. Import Tables:
   1. To import tables, click on the "Table" button in the "Import & Link" group.
   2. In the "Get External Data" dialog, select "Access" as the file type.
   3. Browse and select the old Access database file from which you want to export tables.
   4. Click "Import."
5. Import Queries:
   1. To import queries, click on the "Query" button in the "Import & Link" group.
   2. Follow the same steps as for tables, selecting the old Access database file and importing queries.
6. Choose Tables and Queries:
   1. In the "Import Objects" dialog, select the specific tables and queries you want to import into the new database.
   2. You can select multiple objects by holding down the Ctrl key while clicking.
   3. Click "OK" to begin the import process.
7. Set Import Options (if needed):
   1. Depending on your specific requirements, you may need to configure import options for each table/query.
   2. Access will provide options for renaming objects, specifying data types, and more.
   3. Review and adjust these options as necessary.
8. Complete the Import:
   1. Click "OK" or "Finish" to complete the import process. Access will copy the selected tables and queries into your new database.
9. Save the New Database:
   1. Don't forget to save your new database after importing the tables and queries.\
10. Verify Data and Functionality:
    1. Open and test your new database to ensure that the tables and queries were imported successfully and that they function as expected.

This process allows you to copy tables and queries from one Access database to another. Ensure that you have the necessary permissions and rights to access and modify both databases.